﻿using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Data;
using System.Collections;

namespace app_common
{
    public static class APP_SQL_COMMAND
    {
        public static Hashtable ExecuteNonQuery(string sql)
        {
            string[] arrsql = sql.Split(new Char[] { ';' });
                
            Hashtable result = new Hashtable();
            NpgsqlTransaction trans = null;
            try
            {
                int effected_row = 0;
                Hashtable hashMsg = APP_CONNECTION.Open();
                if (hashMsg[STATUS_MESSAGE.MESSAGE].ToString() == STATUS_MESSAGE.ERROR) {
                    result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                    result.Add(STATUS_MESSAGE.VALUE, hashMsg[STATUS_MESSAGE.VALUE]);
                    return result;
                }
                trans = APP_CONNECTION.Conn.BeginTransaction();
                foreach (string element in arrsql)
                {
                    if (element.Length > 0)
                    {
                        NpgsqlCommand cmd = new NpgsqlCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = element;
                        cmd.Connection = APP_CONNECTION.Conn;
                        cmd.Transaction = trans;
                        effected_row = cmd.ExecuteNonQuery();
                    }                
                }                
                trans.Commit();                
                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.SUCCESS);
                result.Add(STATUS_MESSAGE.VALUE, effected_row);
            }
            catch (Exception ex)
            {
                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                result.Add(STATUS_MESSAGE.VALUE, ex.Message);
                if (trans != null) {
                    trans.Rollback();
                }

            }
            APP_CONNECTION.Close();
            return result;
        }

        public static Hashtable ExecuteReader(string sql)
        {
            Hashtable result = new Hashtable();
            try
            {
                Hashtable hashMsg = APP_CONNECTION.Open();
                if (hashMsg[STATUS_MESSAGE.MESSAGE].ToString() == STATUS_MESSAGE.ERROR)
                {
                    result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                    result.Add(STATUS_MESSAGE.VALUE, hashMsg[STATUS_MESSAGE.VALUE]);
                    return result;
                }
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = APP_CONNECTION.Conn;
                NpgsqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                dr.Close();
                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.SUCCESS);
                result.Add(STATUS_MESSAGE.VALUE, dt);
            }
            catch (Exception ex)
            {
                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                result.Add(STATUS_MESSAGE.VALUE, ex.Message);
            }
            APP_CONNECTION.Close();
            return result;
        }

        public static Hashtable ExecuteScalar(string sql)
        {
            Hashtable result = new Hashtable();
            try
            {
                object data;
                Hashtable hashMsg = APP_CONNECTION.Open();
                if (hashMsg[STATUS_MESSAGE.MESSAGE].ToString() == STATUS_MESSAGE.ERROR)
                {
                    result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                    result.Add(STATUS_MESSAGE.VALUE, hashMsg[STATUS_MESSAGE.VALUE]);
                    return result;
                }
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = APP_CONNECTION.Conn;
                data = cmd.ExecuteScalar();

                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.SUCCESS);
                result.Add(STATUS_MESSAGE.VALUE, data);
            }
            catch (Exception ex)
            {
                result.Add(STATUS_MESSAGE.MESSAGE, STATUS_MESSAGE.ERROR);
                result.Add(STATUS_MESSAGE.VALUE, ex.Message);
            }
            APP_CONNECTION.Close();
            return result;
        }
    }
}
